﻿using DBUtil.Attributes;
using NUnit.Framework;
using Shouldly;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using DotNetCommon.Extensions;
using DBUtil.Builders;
using DBUtil;

namespace Test.MySql.Inserts.InsertEntity
{
    [TestFixture]
    internal class index : TestBase
    {
        [Table("t_user")]
        public class NormalUser
        {
            [PrimaryKey]
            [Column("id")]
            public int Id { get; set; }
            [Column("name")]
            public string Name { get; set; }
            [Column("age")]
            public int? Age { get; set; }
            [Column("addr")]
            public string Addr { get; set; }
            [Column("birth")]
            public DateTime? Birth { get; set; }
        }
        [Table("testtree")]
        public class TestTree
        {
            [PrimaryKey]
            public int Id { get; set; }
            public int ParentId { get; set; }
            public int Level { get; set; }
            public string Name { get; set; }
            public string Hierarchy { get; set; }
            public List<TestTree> Children { get; set; }
        }
        [Test]
        public void NormalTest()
        {
            DropTable("t_user");
            db.ExecuteSql(@"create table t_user(id int primary key,name varchar(50),age int,addr varchar(200),birth datetime)");

            var sql = "";
            //插入一条
            var builder = db.Insert<NormalUser>().SetEntity(new NormalUser()
            {
                Id = 1,
                Age = 20,
                Name = "小明",
                Addr = "天明路",
                Birth = DateTime.Parse("1996-01-02")
            });
            sql = builder.ToSql();
            sql.ShouldBe("insert into t_user(id,name,age,addr,birth) values(1,'小明',20,'天明路','1996-01-02');");
            sql = builder.ToSql(EnumInsertToSql.ExecuteInserted);
            sql.ShouldBe(@"insert into t_user(id,name,age,addr,birth) values(1,'小明',20,'天明路','1996-01-02');
select id `Id`,name `Name`,age `Age`,addr `Addr`,birth `Birth` from t_user where id = 1;");
            try
            {
                sql = builder.ToSql(EnumInsertToSql.ExecuteIdentity);
                throw new Exception("error");
            }
            catch (Exception ex)
            {
                ex.Message.ShouldBe("非自增主键或已声明插入自增列,无法获取自动生成的主键值!");
            }

            //查入2条
            builder = db.Insert<NormalUser>().SetEntity([new NormalUser()
            {
                Id = 2,
                Age = 20,
                Name = "小红",
                Addr = "天明路",
                Birth = DateTime.Parse("1996-01-02")
            }, new NormalUser
            {
                Id = 3,
                Age = 22,
                Name = "小刚",
                Addr = "天明路",
                Birth = DateTime.Parse("1996-01-02")
            } ]);
            sql = builder.ToSql();
            sql.ShouldBe(@"insert into t_user(id,name,age,addr,birth) values
    (2,'小红',20,'天明路','1996-01-02'),
    (3,'小刚',22,'天明路','1996-01-02');");
            sql = builder.ToSql(EnumInsertToSql.ExecuteInserted);
            sql.ShouldBe(@"insert into t_user(id,name,age,addr,birth) values
    (2,'小红',20,'天明路','1996-01-02'),
    (3,'小刚',22,'天明路','1996-01-02');
select id `Id`,name `Name`,age `Age`,addr `Addr`,birth `Birth` from t_user where id in (2,3);");
            try
            {
                sql = builder.ToSql(EnumInsertToSql.ExecuteIdentity);
                throw new Exception("error");
            }
            catch (Exception ex)
            {
                ex.Message.ShouldBe("非自增主键或已声明插入自增列,无法获取自动生成的主键值!");
            }
        }

        [Test]
        public void Test2()
        {
            //一条insert插入3万多条数据
            db.ExecuteSql(db.Manage.DropTableIfExistSql("testtree"));
            db.ExecuteSql(@"
create table testtree(
	id int primary key,
	parentid int,
	level int,
	name varchar(5000),
	hierarchy varchar(500)
)");
            var index = 1;
            var root = new TestTree
            {
                Id = 0,
                Level = 0,
                Children = new List<TestTree>(),
                Hierarchy = "",
                Name = "",
                ParentId = 0
            };
            Generate(root);
            root.Children.VisitTree(i => i.Children, ctx =>
            {
                ctx.Current.Hierarchy = "/" + ctx.ParentsWithSelf.Select(i => i.Id).ToStringSeparated("/") + "/";
            });
            var list = root.Children.ToFlat(i => i.Children);
            var insert = db.Insert<TestTree>().SetEntity(list);
            var sql = insert.ToSql();
            sql.StartsWith(@"insert into testtree(Id,ParentId,Level,Name,Hierarchy) values
    (1,0,1,'name-1','/1/'),
    (3,1,2,'name-3','/1/3/'),
    (5,3,3,'name-5','/1/3/5/')");
            sql.EndsWith(@"    (32765,32762,14,'name-32765','/2/16386/24578/28674/30722/31746/32258/32514/32642/32706/32738/32754/32762/32765/'),
    (32766,32762,14,'name-32766','/2/16386/24578/28674/30722/31746/32258/32514/32642/32706/32738/32754/32762/32766/');");
            sql = insert.ToSql(EnumInsertToSql.ExecuteInserted);
            sql.StartsWith(@"insert into testtree(Id,ParentId,Level,Name,Hierarchy) values
    (1,0,1,'name-1','/1/'),
    (3,1,2,'name-3','/1/3/'),
    (5,3,3,'name-5','/1/3/5/')");
            sql.EndsWith(@"35,32736,32706,32737,32739,32741,32743,32744,32742,32745,32746,32740,32747,32749,32750,32748,32751,32752,32738,32753,32755,32757,32758,32756,32759,32760,32754,32761,32763,32764,32762,32765,32766);");
            var list2 = insert.ExecuteInsertedList();
            list2.Count.ShouldBe(list.Count);

            var dt = insert.ToDataTable();
            dt.TableName.ShouldBe("testtree");
            dt.Rows.Count.ShouldBe(list.Count);
            dt.Rows[0]["Id"].ShouldBe(1);
            dt.Rows[0]["ParentId"].ShouldBe(0);
            dt.Rows[0]["Level"].ShouldBe(1);
            dt.Rows[0]["Name"].ShouldBe("name-1");
            dt.Rows[0]["Hierarchy"].ShouldBe("/1/");
            TruncateTable("testtree");
            db.BulkCopy(dt);
            var r = db.SelectScalar<int>("select count(1) from testtree");
            r.ShouldBe(list.Count);
            var model = db.SelectModel<TestTree>("select * from testtree order by id desc limit 1");
            model.Id.ShouldBe(list2.Last().Id);
            model.ParentId.ShouldBe(list2.Last().ParentId);
            model.Name.ShouldBe(list2.Last().Name);
            model.Level.ShouldBe(list2.Last().Level);
            model.Hierarchy.ShouldBe(list2.Last().Hierarchy);

            void Generate(TestTree parent)
            {
                if (parent.Level >= 14) return;
                var id1 = index++;
                var name1 = "name-" + id1;
                var id2 = index++;
                var name2 = "name-" + id2;
                parent.Children ??= new List<TestTree>();
                var child1 = new TestTree
                {
                    Id = id1,
                    ParentId = parent.Id,
                    Name = name1,
                    Level = parent.Level + 1
                };
                parent.Children.Add(child1);
                var child2 = new TestTree
                {
                    Id = id2,
                    ParentId = parent.Id,
                    Name = name2,
                    Level = parent.Level + 1
                };
                parent.Children.Add(child2);
                Generate(child1);
                Generate(child2);
            }
        }
    }
}
